<%@ Page language="vb" CodeFile="printInvoicePriceListModification.aspx.vb" Inherits="printInvoicePriceListModification_aspx_vb" %>

<%
    invoicePriceListModificationID = Request.QueryString("invoiceID")
    invoicePriceListModificationID = CLng(invoicePriceListModificationID)
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        dbOpen()
        RecSet = Conn.Execute("SELECT i.invoicePriceListModificationID, i.invoiceAmount, i.invoiceClaim, i.invoiceDate, DATENAME(month, i.invoiceDate) As monthName, DATENAME(year, i.invoiceDate) As yearName, i.expiryDate, i.paymentTerm, i.invoicePrinted, i.date, c.countryID, c.countryCurrency, c.countryCode, c.decimals, p.partnerID, p.partnerName, p.street, p.zipcode, p.city, u.username, co.bankType, co.accountNumber, co.cin, co.vat, (Select TOP 1 salesTax From tblSalesTax st Where st.countryID = c.countryID And st.date <= i.invoiceDate Order By st.date desc) As salesTax FROM ((((tblInvoicePriceListModification AS i INNER JOIN tblCountry AS c ON c.countryID = i.countryID) INNER JOIN tblPartner AS p ON p.partnerID = i.partnerID) INNER JOIN tblUser AS u ON u.userID = i.userID) INNER JOIN tblCompany co ON co.countryID = c.countryID) WHERE i.invoicePriceListModificationID = " & invoicePriceListModificationID & "")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
        Else
            invoicePriceListModificationID = RecSet.Fields("invoicePriceListModificationID").Value
            invoiceAmount = RecSet.Fields("invoiceAmount").Value
            invoiceClaim = RecSet.Fields("invoiceClaim").Value
            invoiceDate = RecSet.Fields("invoiceDate").Value
            monthName = RecSet.Fields("monthName").Value
            yearName = RecSet.Fields("yearName").Value
            expiryDate = RecSet.Fields("expiryDate").Value
            paymentTerm = RecSet.Fields("paymentTerm").Value
            invoicePrinted = RecSet.Fields("invoicePrinted").Value
            __date = RecSet.Fields("date").Value
            countryID = RecSet.Fields("countryID").Value
            countryCurrency = RecSet.Fields("countryCurrency").Value
            countryCode = RecSet.Fields("countryCode").Value
            decimals = RecSet.Fields("decimals").Value
            partnerID = RecSet.Fields("partnerID").Value
            partnerName = RecSet.Fields("partnerName").Value
            street = RecSet.Fields("street").Value
            zipcode = RecSet.Fields("zipcode").Value
            city = RecSet.Fields("city").Value
            username = RecSet.Fields("username").Value
            bankType = RecSet.Fields("bankType").Value
            accountNumber = RecSet.Fields("accountNumber").Value
            cin = RecSet.Fields("cin").Value
            vat = RecSet.Fields("vat").Value
            salesTax = RecSet.Fields("salesTax").Value
            '**********************
            If countryID = 4 Then
                If (__date <= Date.Parse("2010-06-30")) Then
                    salesTax = 22
                ElseIf (__date > Date.Parse("2010-06-30") And __date < Date.Parse("2013-01-08")) Then
                    salesTax = 23
                Else
                    salesTax = 24
                End If
            End If
            '**********************
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        vehicleCount = 0
        dbOpen()
        RecSet = Conn.Execute("Select vehicleID From tblInvoicePriceListModificationContent Where invoicePriceListModificationID = " & invoicePriceListModificationID & " Group By vehicleID")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                vehicleCount = vehicleCount + 1
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        salesTaxAmount = 0
        salesTaxAmount = (CDbl(invoiceAmount) * CDbl(salesTax)) / 100
        invoiceAmount = invoiceAmount + salesTaxAmount
        invoiceAmount = FormatNumber(CDbl(invoiceAmount), decimals)
        CompanyAddressSQL()
        OverPrint()
        Response.Write("<div class=noprint>")
        Response.Write("<table>")
        Response.Write("<tr>")
        Response.Write("<td class='headline'>")
        Response.Write("<form>")
        Response.Write("<input type='button' onClick='javascript:print();' value='Print'>&nbsp; ")
        If CDbl(invoicePrinted) = 0 Then 
            Response.Write("<input type='button' value='Set invoice as printed' onclick=""{location.href='printInvoicePriceListModification.aspx?do=save&invoiceID=" & invoicePriceListModificationID & "';} return false;"">")
        Else
            Response.Write("<font color='#fcbc00'>" & subDo & "</font>")
        End IF
        Response.Write("</form>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Response.Write("</div>")
        For printCounter = 1 To 2
            Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%' style='page-break-after: always'>")
            Response.Write("<tr>")
            Response.Write("<td colspan='3'>")
            CompanyAddress()
            Response.Write("</td>")
            Response.Write("<td colspan='2'>")
            Response.Write("<table cellpadding='2' cellspacing='0'>")
            Response.Write("<tr><td valign='top' class='printBold'>" & partnerName & "</td></tr>")
            Response.Write("<tr><td valign='top' class='printRegular'>" & street & "</td></tr>")
            Response.Write("<tr><td valign='top' class='printRegular'>" & countryCode & "-" & zipcode & " " & city & "</td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td class='headlineBig' colspan='5' align='center'>")
            Response.Write("INVOICE")
            If printCounter = 2 Then 
                Response.Write(" - COPY")
            End IF
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td width='125' valign='top' class='printBold'>Customer number</td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Invoice number</td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Date of invoice</td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Date of expiration</td>")
            Response.Write("</tr>")
            Response.Write("<tr>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & partnerID & "</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & invoicePriceListModificationID & "</td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & invoiceDate & "</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & expiryDate & "</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td width='125' valign='top' class='printBold'>Our referee</td>")
            Response.Write("<td width='125' valign='top' class='printBold'></td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printBold'>" & bankType & "</td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Payment term</td>")
            Response.Write("</tr>")
            Response.Write("<tr>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & username & "</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'></td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & accountNumber & "</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & paymentTerm & "</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td colspan='5'>")
            Response.Write("<table class='fullFrame' width='100%' cellpadding='3' cellspacing='0' height='200'>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' class='printRegular'>")
            Response.Write("This invoice is based on " & vehicleCount & " lease contracts due to price modification.<br>")
            Response.Write("For more details, please see the attached specification.")
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' class='printBold' colspan='2'>Corporate identity number</td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Price:</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & FormatNumber(CDbl(invoiceAmount) - salesTaxAmount, 2) & " " & countryCurrency & "</td>")
            Response.Write("</tr>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' class='printRegular' colspan='2'>" & cin & "</td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printBold'>VAT (" & salesTax & "%):</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & FormatNumber(salesTaxAmount, 2) & " " & countryCurrency & "</td>")
            Response.Write("</tr>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' class='printRegular' colspan='2'></td>")
            Response.Write("<td width='30'></td>")
            Response.Write("<td width='125' valign='top' class='printBold'>Invoice amount:</td>")
            Response.Write("<td width='125' valign='top' class='printRegular'>" & FormatNumber(CDbl(invoiceAmount), 2) & " " & countryCurrency & "</td>")
            Response.Write("</tr>")
            If Len(vat) > 0 Then 
                Response.Write("<tr>")
                Response.Write("<td valign='top' class='printBold' colspan='2'>VAT registration number</td>")
                Response.Write("</tr>")
                Response.Write("<tr>")
                Response.Write("<td valign='top' class='printRegular' colspan='2'>" & vat & "</td>")
                Response.Write("</tr>")
            End IF
            Response.Write("</table>")
        Next
        For printCounter = 1 To 2
            rowNumber = 1
            dbOpen()
            RecSet = Conn.Execute("Select iplmc.leaseCurrentDate, v.regnr, v.vinNumber, v.contractNumber, b.brand, m.model, p.product, pl1.price As oldPrice, pl2.price As newPrice, pl2.priceListDuration, d.dealer, c.countryCurrency, (Select Count(ic.vehicleID) From tblInvoiceContent ic Where ic.vehicleID = iplmc.vehicleID AND ic.invoiceID <= iplmc.invoiceID) As vehicleCount From ((((((((tblInvoicePriceListModificationContent iplmc INNER JOIN tblVehicle v ON v.vehicleID = iplmc.vehicleID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIn tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl1 ON pl1.priceListID = iplmc.oldPriceListID) INNER JOIN tblPriceList pl2 ON pl2.priceListID = iplmc.newPriceListID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where iplmc.invoicePriceListModificationID = " & invoicePriceListModificationID & " Order by v.contractNumber, iplmc.leaseCurrentDate")
            If Not RecSet.EOF Then 
                Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%' style='page-break-after: always'>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr>")
                Response.Write("<td class='headlineBig' colspan='5' align='center'>")
                Response.Write("LEASING - PRICE MODIFICATION")
                Response.Write("</td>")
                Response.Write("</tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr><td><br></td></tr>")
                Response.Write("<tr>")
                Response.Write("<td colspan='5'>")
                Response.Write("<table class='fullFrame' width='100%' cellpadding='3' cellspacing='0' height='100%'>")
                Response.Write("<tr valign='top'>")
                Response.Write("<td class='printBold'>Contract number</td>")
                Response.Write("<td class='printBold'>Licence number</td>")
                Response.Write("<td class='printBold'>VIN-number</td>")
                'Response.Write "<td class='printBold'>Car make</td>"
                Response.Write("<td class='printBold'>Product</td>")
                Response.Write("<td class='printBold'>Period</td>")
                'Response.Write "<td class='printBold'>Car dealer</td>"
                Response.Write("<td class='printBold'>Old price</td>")
                Response.Write("<td class='printBold'>New price</td>")
                Response.Write("<td class='printBold'>Difference in prices</td>")
                Response.Write("<td class='printBold'></td>")
                Response.Write("</tr>")
                Do Until RecSet.EOF
                    If rowNumber = 20 Then 
                        Response.Write("</table>")
                        Response.Write("</td>")
                        Response.Write("</tr>")
                        Response.Write("</table>")
                        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='100%' style='page-break-after: always'>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr>")
                        Response.Write("<td class='headlineBig' colspan='5' align='center'>")
                        Response.Write("LEASING - PRICE MODIFICATION")
                        Response.Write("</td>")
                        Response.Write("</tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr><td><br></td></tr>")
                        Response.Write("<tr>")
                        Response.Write("<td colspan='5'>")
                        Response.Write("<table class='fullFrame' width='100%' cellpadding='3' cellspacing='0' height='100%'>")
                        Response.Write("<tr valign='top'>")
                        Response.Write("<td class='printBold'>Contract number</td>")
                        Response.Write("<td class='printBold'>Licence number</td>")
                        Response.Write("<td class='printBold'>VIN-number</td>")
                        'Response.Write "<td class='printBold'>Car make</td>"
                        Response.Write("<td class='printBold'>Product</td>")
                        Response.Write("<td class='printBold'>Duration</td>")
                        'Response.Write "<td class='printBold'>Car dealer</td>"
                        Response.Write("<td class='printBold'>Old price</td>")
                        Response.Write("<td class='printBold'>New price</td>")
                        Response.Write("<td class='printBold'>Difference in prices</td>")
                        Response.Write("<td class='printBold'></td>")
                        Response.Write("</tr>")
                        rowNumber = 1
                    End IF
                    leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    product = RecSet.Fields("product").Value
                    oldPrice = RecSet.Fields("oldPrice").Value
                    newPrice = RecSet.Fields("newPrice").Value
                    priceListDuration = RecSet.Fields("priceListDuration").Value
                    dealer = RecSet.Fields("dealer").Value
                    countryCurrency = RecSet.Fields("countryCurrency").Value
                    vehicleCount = RecSet.Fields("vehicleCount").Value
                    leaseCurrentDate = Left(leaseCurrentDate, 7)
                    price = CDbl(newPrice) - CDbl(oldPrice)
                    Response.Write("<tr valign='top'>")
                    Response.Write("<td class='printRegular'>" & contractNumber & "</td>")
                    Response.Write("<td class='printRegular'>" & regnr & "</td>")
                    Response.Write("<td class='printRegular'>" & vinNumber & "</td>")
                    'Response.Write "<td class='printRegular'>" & brand & " " & model & "</td>"
                    Response.Write("<td class='printRegular'>" & product & "</td>")
                    'Response.Write "<td class='printRegular'>" & vehicleCount & "/" & priceListDuration & "</td>"
                    Response.Write("<td class='printRegular'>" & leaseCurrentDate & "</td>")
                    'Response.Write "<td class='printRegular'>" & dealer & "</td>"
                    Response.Write("<td align='right' class='printRegular' nowrap='nowrap'>" & FormatNumber(CDbl(oldPrice), 2) & "</td>")
                    Response.Write("<td align='right' class='printRegular' nowrap='nowrap'>" & FormatNumber(CDbl(newPrice), 2) & "</td>")
                    Response.Write("<td align='right' class='printRegular' nowrap='nowrap'>" & FormatNumber(price, 2) & "</td>")
                    Response.Write("<td class='printRegular' nowrap='nowrap'>" & countryCurrency & "</td>")
                    Response.Write("</tr>")
                    rowNumber = rowNumber + 1
                    RecSet.MoveNext()
                Loop
                Response.Write("<tr><td height='100%'></td></tr>")
                Response.Write("</table>")
                Response.Write("</td>")
                Response.Write("</tr>")
                Response.Write("</table>")
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
        Next
        Under()
    ElseIf Request.QueryString("do") = "save" Then 
        dbOpen()
        Conn.Execute("Update tblInvoicePriceListModification Set invoicePrinted = 1 Where invoicePriceListModificationID = " & invoicePriceListModificationID & "")
        dbOpen()
        Response.Redirect("printInvoicePriceListModification.aspx?subDo=%20-%20THIS%20INVOICE%20IS%20%SET%20AS%20PRINTED&invoiceID=" & invoicePriceListModificationID & "")
    End IF
%>
